﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using HsTec.Mobile.Green.Model;

using HsTec.Mobile.Green.Util;
using System.Data.SqlClient;

namespace HsTec.Mobile.Green.BLL
{
    /// <summary>
    /// 业务逻辑类ReportTemplate 的摘要说明。
    /// </summary>
    public class ReportTemplateBLL
    {
        private int iDepth = 0;
        private readonly HsTec.Mobile.Green.DAL.ReportTemplateDAL dal = new HsTec.Mobile.Green.DAL.ReportTemplateDAL();
        public ReportTemplateBLL()
        { }

        #region  成员方法
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string RT_IDs)
        {
            return dal.Exists(RT_IDs);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(HsTec.Mobile.Green.Model.ReportTemplate model)
        {
            return dal.Add(model);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(HsTec.Mobile.Green.Model.ReportTemplate model)
        {
            return dal.Update(model);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(int uid)
        {
            return dal.Delete(uid);
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public HsTec.Mobile.Green.Model.ReportTemplate GetModel(int RT_ID)
        {
            return dal.GetModel(RT_ID);
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataView GetList(string strWhere)
        {
            return dal.GetList(strWhere);
        }
        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataView GetList(int Top, string strWhere, string filedOrder)
        {
            return dal.GetList(Top, strWhere, filedOrder);
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<HsTec.Mobile.Green.Model.ReportTemplate> GetModelList(string strWhere)
        {
            DataView dv = dal.GetList(strWhere);
            return DataTableToList(dv.Table);
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public List<HsTec.Mobile.Green.Model.ReportTemplate> DataTableToList(DataTable dt)
        {
            List<HsTec.Mobile.Green.Model.ReportTemplate> modelList = new List<HsTec.Mobile.Green.Model.ReportTemplate>();
            int rowsCount = dt.Rows.Count;
            if (rowsCount > 0)
            {
                HsTec.Mobile.Green.Model.ReportTemplate model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new HsTec.Mobile.Green.Model.ReportTemplate();
                    if (dt.Rows[n]["RT_ID"].ToString() != "")
                    {
                        model.RT_ID = int.Parse(dt.Rows[n]["RT_ID"].ToString());
                    }
                    model.FieldName = dt.Rows[n]["FieldName"].ToString();
                    model.FieldAlias = dt.Rows[n]["FieldAlias"].ToString();
                    if (dt.Rows[n]["FieldType"].ToString() != "")
                    {
                        model.FieldType = int.Parse(dt.Rows[n]["FieldType"].ToString());
                    }
                    if (dt.Rows[n]["FieldSelect"].ToString() != "")
                    {
                        model.FieldSelect = int.Parse(dt.Rows[n]["FieldSelect"].ToString());
                    }
                    model.SelectContent = dt.Rows[n]["SelectContent"].ToString();
                    model.EecelCell = dt.Rows[n]["EecelCell"].ToString();
                    model.Remark = dt.Rows[n]["Remark"].ToString();
                    if (dt.Rows[n]["FieldParent"].ToString() != "")
                    {
                        model.FieldParent = int.Parse(dt.Rows[n]["FieldParent"].ToString());
                    }
                    if (dt.Rows[n]["FieldChildCount"].ToString() != "")
                    {
                        model.FieldChildCount = int.Parse(dt.Rows[n]["FieldChildCount"].ToString());
                    }
                    if (dt.Rows[n]["FieldNo"].ToString() != "")
                    {
                        model.FieldNo = int.Parse(dt.Rows[n]["FieldNo"].ToString());
                    }
                    model.FieldFormula = dt.Rows[n]["FieldFormula"].ToString();
                    if (dt.Rows[n]["FieldTemplate"].ToString() != "")
                    {
                        model.FieldTemplate = int.Parse(dt.Rows[n]["FieldTemplate"].ToString());
                    }
                    modelList.Add(model);
                }
            }
            return modelList;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataView GetAllList()
        {
            return GetList("");
        }

        #endregion  成员方法

        /// <summary>
        /// 根据父节点ID查找所有的孩子
        /// </summary>
        /// <param name="uid">父节点ID</param>
        /// <returns>List</returns>
        public List<HsTec.Mobile.Green.Model.ReportTemplate> GetModelListByID(int uid)
        {
            return this.GetModelList("FieldParent=" + uid);
        }

        public List<string> getContent(string sContent)
        {
            string[] ary = sContent.Split(',');
            List<string> list = new List<string>();
            foreach (string str in ary)
            {
                list.Add(str);
            }
            return list;
        }

        /// <summary>
        /// 生成模板
        /// </summary>
        /// <param name="sid">模板ID</param>
        /// <returns>返回是否成功</returns>
        public int GenerationTemplates(string sid)
        {
            HsTec.Mobile.Green.Model.ReportTemplate rtRoot = new ReportTemplate();
            rtRoot = this.GetModel(int.Parse(sid));
            //处理包含多个子表的节点
            string sTableType = SQLHelper.ExecuteString("select count(*) from tb_ReportTemplate where FieldParent='" + sid + "' and FieldChildCount>0");
            if (sTableType != "0")
            {
                string tmp = String.Empty;
                //0.检查是否存在 rt_字段名 ，如有需删除
                string tbExist = SQLHelper.ExecuteString("SELECT ISNULL(OBJECTPROPERTY(OBJECT_ID('rv_" + rtRoot.FieldName + "'),   'IsUserView'),   0)");
                if (tbExist == "1")
                    SQLHelper.ExecuteString("drop VIEW rv_" + rtRoot.FieldName);
                string sql1 = "CREATE  VIEW [rv_" + rtRoot.FieldName + "] AS ";
                string sql = String.Empty;
                //表示含有多个子表
                List<ReportTemplate> list = this.GetModelListByID(int.Parse(sid));
                foreach (ReportTemplate rt in list)
                {
                    string sField = String.Empty;
                    //获取字段 
                    List<ReportTemplate> fields = this.GetModelListByID(rt.RT_ID);
                    sField += "[ID]";
                    foreach (ReportTemplate rttmp in fields)
                    {
                        sField += ",[" + rttmp.FieldName + "]";
                    }
                    if (String.IsNullOrEmpty(sql))
                        sql += ("select Area_ID,Years,'" + rt.FieldAlias + "' as 分项," + sField + " from rt_" + rt.FieldName + " ");
                    else
                        sql += ("union select Area_ID,Years,'" + rt.FieldAlias + "' as 分项," + sField + " from rt_" + rt.FieldName + " ");
                }
                return SQLHelper.ExecuteNonQuery(sql1 + sql);
            }
            else
            {
                //-------------------------------------------------------------------
                //该方法实现部分需要转移到DAL中
                //-------------------------------------------------------------------
                string tmp = String.Empty;
                //0.检查是否存在 rt_字段名 ，如有需删除
                string tbExist = SQLHelper.ExecuteString("SELECT ISNULL(OBJECTPROPERTY(OBJECT_ID('rt_" + rtRoot.FieldName + "'),   'IsUserTable'),   0)");
                if (tbExist == "1")
                    try
                    {
                        SQLHelper.ExecuteString("drop table rt_" + rtRoot.FieldName);
                    }
                    catch
                    { }
                //1.取出该模板下的所有叶子节点 要求按排序号生成
                List<ReportTemplate> list = new List<ReportTemplate>();
                this.GetChilds(int.Parse(sid), list);
                //2.根据已有的字段生成动态的表格 表格格式 rt_字段名
                //2.1 组合创建表语句
                StringBuilder sSql = new StringBuilder();
                sSql.Append("CREATE TABLE [rt_" + rtRoot.FieldName + "]");
                sSql.Append("(");
                sSql.Append("[ID] [int]  IDENTITY (1, 1) PRIMARY key  NOT NULL");
                sSql.Append(",[Area_ID] [int] NULL");
                sSql.Append(",[Years] [varchar](6) NULL");
                sSql.Append(",[AddUser] [int]  NULL");
                sSql.Append(",[AddDate] [datetime]  NULL");
                foreach (ReportTemplate rt in list)
                {
                    //表中数据字段类型的处理
                    tmp = "[nvarchar](50)";
                    sSql.Append(",[" + rt.FieldName + "] " + tmp + " NULL");
                }
                sSql.Append(")");

                SQLHelper.myconn.Open();
                SqlCommand command = SQLHelper.myconn.CreateCommand();
                SqlTransaction transaction;
                transaction = SQLHelper.myconn.BeginTransaction();
                command.Connection = SQLHelper.myconn;
                command.Transaction = transaction;
                try
                {
                    command.CommandText = sSql.ToString();
                    command.ExecuteNonQuery();
                    command.CommandText = "update tb_ReportTemplate set FieldTemplate='" + sid + "' where RT_ID='" + sid + "' and FieldParent<>0";
                    command.ExecuteNonQuery();
                    //处理所有的孩子节点 修改模板节点为本节点
                    DataView mydv = SQLHelper.ExecuteDataView("select RT_ID from tb_ReportTemplate where FieldParent='" + sid + "'", SQLHelper.myconn, transaction);
                    for (int i = 0; i < mydv.Count; i++)
                    {
                        command.CommandText = "update tb_ReportTemplate set FieldTemplate='" + sid + "' where RT_ID='" + mydv[i]["RT_ID"].ToString() + "'";
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    return 1;
                }
                catch
                {
                    transaction.Rollback();
                    return 0;
                }
                finally
                {
                    transaction.Dispose();
                    SQLHelper.myconn.Close();
                }
            }
        }
        private void GetChilds(int uid, List<HsTec.Mobile.Green.Model.ReportTemplate> fieldList)
        {
            List<HsTec.Mobile.Green.Model.ReportTemplate> list = this.GetModelListByID(uid);
            foreach (HsTec.Mobile.Green.Model.ReportTemplate rt in list)
            {
                if (rt.FieldChildCount == 0)
                    fieldList.Add(rt);
                GetChilds(rt.RT_ID, fieldList);
            }
        }

        public void GenerationForm(int uid)
        {
            //0.得到一个关于模板树的集合
            Dictionary<int, int[]> mydic = new Dictionary<int, int[]>();
            GenerationTable(uid, mydic, 0);
            //1.创建表格字符串
            Queue<ReportTemplate> queuelist = new Queue<ReportTemplate>();
            StringBuilder sForm = new StringBuilder();
            sForm.AppendLine("<table>");
            List<ReportTemplate> list = this.GetModelListByID(uid);
            foreach (ReportTemplate rt in list)
            {
                queuelist.Enqueue(rt);
            }
            WriteFromTr(queuelist, mydic, sForm);
            sForm.AppendLine("</table>");
            //2.输出表格
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="queuelist"></param>
        /// <param name="mydic"></param>
        /// <param name="sForm"></param>
        /// <returns></returns>
        private StringBuilder WriteFromTr(Queue<ReportTemplate> queuelist, Dictionary<int, int[]> mydic, StringBuilder sForm)
        {
            sForm.AppendLine("<tr>");
            ReportTemplate rtTmp = new ReportTemplate();
            int iTmp = queuelist.Count;
            for (int i = 0; i < iTmp; i++)
            {
                rtTmp = queuelist.Dequeue();
                List<ReportTemplate> list = this.GetModelListByID(rtTmp.RT_ID);
                foreach (ReportTemplate rt in list)
                {
                    queuelist.Enqueue(rt);
                }
                sForm.Append("<td ");
                if (list.Count == 0)
                    sForm.Append(" rowspan=" + (iDepth - mydic[rtTmp.RT_ID][1] + 1));
                sForm.AppendLine(" colspan=" + mydic[rtTmp.RT_ID][0] + ">");
                sForm.AppendLine(rtTmp.FieldName);
                sForm.AppendLine("</td>");
            }
            sForm.AppendLine("</tr>");
            if (queuelist.Count != 0)
                WriteFromTr(queuelist, mydic, sForm);
            return sForm;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="uid"></param>
        /// <param name="treeDic"></param>
        /// <param name="iDep"></param>
        /// <param name="iDeptNum"></param>
        /// <returns></returns>
        private int GenerationTable(int uid, Dictionary<int, int[]> treeDic, int iDep)
        {
            treeDic.Add(uid, new int[] { 0, 0 });
            List<ReportTemplate> list = this.GetModelListByID(uid);
            int iCount = list.Count;
            iDep++;
            if (iDep > iDepth)
                iDepth = iDep;
            foreach (ReportTemplate rt in list)
            {
                iCount += GenerationTable(rt.RT_ID, treeDic, iDep);
            }
            treeDic[uid] = new int[] { iCount, iDep };
            return iCount;
        }

        /// <summary>
        /// 获取生成GridView所需的DataView
        /// </summary>
        /// <param name="p"></param>
        /// <param name="p_2"></param>
        public DataView getGridViewInfo(string sTableID, string sItemID, string sWhere)
        {
            DataView tmp = new DataView();
            StringBuilder sql = new StringBuilder();

            //分别处理不同的两种情况 一种是包含了多个字表的大表 还有一种是只包含一个表
            if (String.IsNullOrEmpty(sItemID))
            {
                //判断是否有子表 即是否包含子数据表 如果包含子数据表需要在试图中完成查询
                List<ReportTemplate> childList = this.GetModelList("FieldParent='" + sTableID + "' and FieldChildCount>0");
                if (childList.Count == 0)
                {
                    ReportTemplate parent = dal.GetModel(int.Parse(sTableID));
                    sql.Remove(0, sql.Length);
                    //获取表所对应的字段
                    tmp = dal.GetList(6, "FieldParent = '" + sTableID + "' and FieldChildCount=0", "FieldNo");
                    //组织SQL语句
                    sql.Append(" select [rt_" + parent.FieldName + "].ID as 全选,[Name] as [地区],[Years] as 年月");
                    for (int i = 0; i < tmp.Count; i++)
                    {
                        sql.Append("," + tmp[i]["FieldName"].ToString() + " as [" + tmp[i]["FieldAlias"].ToString() + "]");
                    }
                    sql.Append(" from [rt_" + parent.FieldName + "] left join Area on Area_ID = Area.ID " + sWhere);
                    return SQLHelper.ExecuteDataView(sql.ToString());
                }
                else
                {
                    ReportTemplate parent = dal.GetModel(int.Parse(sTableID));
                    sql.Remove(0, sql.Length);
                    //获取表所对应的字段
                    tmp = dal.GetList("FieldParent = '" + childList[0].RT_ID + "' and FieldChildCount=0");
                    //组织SQL语句
                    sql.Append(" select [ID] as 全选,[分项],Name as [地区],[Years] as 年月");
                    for (int i = 0; i < tmp.Count; i++)
                    {
                        sql.Append("," + tmp[i]["FieldName"].ToString() + " as [" + tmp[i]["FieldAlias"].ToString() + "]");
                    }
                    sql.Append(" from [rv_" + parent.FieldName + "] left join Area on Area_ID = Area.ID " + sWhere);
                    return SQLHelper.ExecuteDataView(sql.ToString());
                }
            }
            else
            {
                ReportTemplate parent = dal.GetModel(int.Parse(sItemID));
                sql.Remove(0, sql.Length);
                //获取表所对应的字段
                tmp = dal.GetList("FieldParent = '" + sItemID + "' and FieldChildCount=0");
                //组织SQL语句
                sql.Append(" select [rt_" + parent.FieldName + "].ID as 全选,Name as [地区],[Years] as 年月");
                for (int i = 0; i < tmp.Count; i++)
                {
                    sql.Append("," + tmp[i]["FieldName"].ToString() + " as [" + tmp[i]["FieldAlias"].ToString() + "]");
                }
                sql.Append(" from [rt_" + parent.FieldName + "] left join Area on Area_ID = Area.ID " + sWhere);
                return SQLHelper.ExecuteDataView(sql.ToString());
            }
        }

        public DataView GetAreaList()
        {
            return SQLHelper.ExecuteDataView("select * from Area where ParentID=10");
        }

        public object GetBindList(string strWhere)
        {
            return dal.GetBindList(strWhere);
        }

        public int DeleteInfo(string sTable, string sID)
        {
            string stable = SQLHelper.ExecuteString("select FieldName from tb_ReportTemplate where RT_ID='" + sTable + "'");
            return SQLHelper.ExecuteNonQuery("delete from rt_" + stable + " where ID in (" + sID.Substring(0, sID.Length - 1) + ")");
        }
    }
}

